from .CreateTable import uploader, video, comments, dm
from .CreateTable import metadata

from GetBilibiliData.GetBilibiliUploaderInfo import get_video_list_from_uploader_id
from GetBilibiliData.GetBilibiliVideoInfo import get_av_vid_comment_number_and_tags_from_bv
from GetBilibiliData.GetBilibiliVideoInfo import get_comments_and_replies_from_av_and_bv
from GetBilibiliData.GetBilibiliVideoInfo import get_dm_from_vid_and_bv

from sqlalchemy import create_engine
from sqlalchemy import insert, select, update, and_
from sqlalchemy.sql.dml import Insert, Update
from sqlalchemy.sql.selectable import Select
from sqlalchemy.engine.result import ResultProxy, RowProxy
from sqlalchemy.engine.base import Engine, Connection

import datetime


def gather_uploader_info(connection: Connection) -> None:
    """
    将我需要的UP主的信息插入数据库中。
    :param connection: 一个数据库连接，数据库中必须已经创建好了对应的表（up，video，comments，dm）
    :return: None
    """
    up = {
        10330740: '观察者网',
        456664753: '央视新闻',
        10303206: '环球时报',
        483787858: '环球网',
        222103174: '小央视频',
        54992199: '观视频工作室',
    }

    for uid in up:
        name = up[uid]
        sel = select([uploader]).where(uploader.c.uid == uid)  # type: Select
        sel_rp = connection.execute(sel)  # type: ResultProxy
        if sel_rp.first():
            continue

        ins = insert(uploader).values(  # type: Insert
            uid=uid,
            name=name
        )
        res = connection.execute(ins)  # type: ResultProxy
        print('up主信息插入：' + str(res.inserted_primary_key))


def gather_video_info_for_single_uploader(connection: Connection, uid: int, required_tags: list,
                                          start_time: datetime.datetime, end_time: datetime.datetime) -> None:
    """
    根据UP主的UID，爬取一定之间段内，这个UP上传的包含指定标签的所有视频的信息，并储存。
    :param connection: 一个数据库连接，必须已经创建好了相关数据表
    :param uid: UP主的UID
    :param required_tags: 最终插入数据库的视频的标签至少有一个出现在required_tags中
    :param start_time: 需要的视频的最早上传时间
    :param end_time: 需要的视频的最晚上传时间
    :return: None
    """
    def __filter_video_tags(bv: str, wanted_tags: list) -> bool:
        _, _, cnt, tags = get_av_vid_comment_number_and_tags_from_bv(bv=bv)
        if cnt == -1:
            return False
        real_tags = []
        for t in tags:  # type: dict
            real_tags.append(t['tag_name'])

        for t1 in real_tags:  # type: str
            for t2 in wanted_tags:  # type: str
                if t1.find(t2) != -1 or t2.find(t1) != -1:
                    print(real_tags)
                    return True
        return False

    res = get_video_list_from_uploader_id(uid=f'{uid}', start_time=start_time, end_time=end_time)

    for v in res:  # type: dict
        if __filter_video_tags(bv=v['bvid'], wanted_tags=required_tags):
            sel = select([video.c.av]).where(video.c.av == v['aid'])  # type: Select
            sel_rp = connection.execute(sel)  # type: ResultProxy

            if sel_rp.first():
                upd = update(video).values(  # type: Update
                    comment_count=v['comment'],
                    play_count=v['play'],
                    title=v['title'],
                    description=v['description'],
                )
                upd = upd.where(video.c.av == v['aid'])
                upd_rp = connection.execute(upd)  # type: ResultProxy
                print(upd_rp.last_updated_params())
            else:
                ins = insert(video).values(  # type: Insert
                    av=v['aid'],
                    bv=v['bvid'],
                    comment_count=v['comment'],
                    play_count=v['play'],
                    title=v['title'],
                    description=v['description'],
                    uploader_id=uid,
                    upload_time=datetime.datetime.fromtimestamp(v['created']),
                )

                ins_res = connection.execute(ins)  # type: ResultProxy
                print(ins_res.inserted_primary_key)


def gather_video_info_for_all_uploader(connection: Connection, start_time: datetime.datetime,
                                       end_time: datetime.datetime) -> None:
    """
    对于数据库中已经存在的所有UP主，爬取他们在一定时间范围内上传的视频的信息，并储存。
    :param connection: 数据库连接，相关数据表必须已经创建好
    :param start_time: 开始时间
    :param end_time: 结束时间
    :return: None
    """
    up_sel = select([uploader.c.uid, uploader.c.name])  # type: Select
    rp = connection.execute(up_sel)  # type: ResultProxy
    required_tags = ['福奇', '肺炎', '新冠', '疫情', '病毒', '蝙蝠', 'COVID-19', 'COVID19']  # 用这些标签来识别与COVID19相关的视频

    for r in rp:  # type: RowProxy
        print(f'现在获取 {r.name} 的视频列表')
        gather_video_info_for_single_uploader(connection=connection, uid=r.uid, required_tags=required_tags,
                                              start_time=start_time, end_time=end_time)


def gather_comment_info_for_single_video(connection: Connection, av: int, bv: str, comment_total: int) -> None:
    """
    对于单个视频，爬取它的所有评论并储存。
    :param connection: 数据库连接，相关数据表必须已经创建
    :param av: 视频的AV号
    :param bv: 视频的BV号
    :param comment_total: 视频评论总数（作为识别数据是否已经获取完整的依据）
    :return: None
    """

    def __insert_comment(__rp_id: int, __video_id: int, __likes: int, __root_comment: int, __content: str,
                         __comment_time: datetime.datetime) -> int:
        sel = select([comments]).where(comments.c.rp_id == __rp_id)  # type: Select
        rp = connection.execute(sel)  # type: ResultProxy
        if rp.first():
            upd = update(comments).values(  # type: Update
                likes=__likes,
            )
            upd = upd.where(comments.c.rp_id == __rp_id)  # type: Update
            connection.execute(upd)
            return __rp_id

        ins = insert(comments).values(  # type: Insert
            rp_id=__rp_id,
            video_id=__video_id,
            likes=__likes,
            root_comment=__root_comment,
            content=__content,
            comment_time=__comment_time,
        )
        rp = connection.execute(ins)  # type: ResultProxy
        return rp.inserted_primary_key

    cts = get_comments_and_replies_from_av_and_bv(av=str(av), bv=bv, comment_total=comment_total)
    for c in cts:  # type: dict
        ins_id = __insert_comment(
            __rp_id=c['rpid'],
            __video_id=c['oid'],
            __likes=c['like'],
            __root_comment=-1,
            __content=c['content']['message'],
            __comment_time=datetime.datetime.fromtimestamp(c['ctime']),
        )
        print(ins_id)

        if c.get('replies'):
            for r in c['replies']:  # type: dict
                ins_id = __insert_comment(
                    __rp_id=r['rpid'],
                    __video_id=r['oid'],
                    __likes=r['like'],
                    __root_comment=r['root'],
                    __content=r['content']['message'],
                    __comment_time=datetime.datetime.fromtimestamp(r['ctime'])
                )
                print(ins_id)


def gather_comment_info_for_all_video(connection: Connection) -> None:
    """
    对于数据库中已经存在的所有视频信息，爬取他们的评论并储存。
    :param connection: 数据库连接，相关数据表必须已经创建完成。
    :return: None
    """
    video_sel = select([video.c.av, video.c.bv, video.c.comment_count])  # type: Select
    video_rp = connection.execute(video_sel)  # type: ResultProxy

    for v in video_rp:  # type: RowProxy
        gather_comment_info_for_single_video(connection=connection, av=v.av, bv=v.bv, comment_total=v.comment_count)


def gather_dm_info_for_single_video(connection: Connection, av: int, bv: str) -> None:
    """
    爬取某一个视频的弹幕并储存。
    :param connection: 数据库连接，相关数据表必须已经创建。
    :param av: 视频的AV号
    :param bv: 视频的BV号
    :return: None
    """
    _, vid, _, _ = get_av_vid_comment_number_and_tags_from_bv(bv=bv)
    if vid == '':
        return
    dms = get_dm_from_vid_and_bv(vid=vid, bv=bv)
    for d in dms:
        text = d[0]
        prop = d[1]

        sel = select([dm.c.content]).where(and_(dm.c.content == text, dm.c.property == prop))  # type: Select
        rp = connection.execute(sel)  # type: ResultProxy
        if rp.first():
            continue

        ins = insert(dm).values(  # type: Insert
            video_id=av,
            content=text,
            property=prop,
        )
        rp = connection.execute(ins)  # type: ResultProxy
        print(rp.inserted_primary_key)


def gather_dm_info_for_all_video(connection: Connection) -> None:
    """
    对于数据库中已经存在的所有视频，爬取他们的弹幕数据，并储存。
    :param connection:
    :return:
    """
    video_sel = select([video.c.av, video.c.bv])  # type: Select
    video_rp = connection.execute(video_sel)  # type: ResultProxy

    for r in video_rp:  # type: RowProxy
        gather_dm_info_for_single_video(connection=connection, av=r.av, bv=r.bv)


if __name__ == '__main__':
    pass
    # engine = create_engine('sqlite:///../bilibili.db', echo=True, encoding='utf-8')  # type: Engine
    # metadata.create_all(engine)
    # con = engine.connect()
    #
    # gather_uploader_info(connection=con)
    # gather_video_info_for_all_uploader(
    #     connection=con,
    #     start_time=datetime.datetime(year=2020, month=5, day=3, hour=0, minute=0, second=0),
    #     end_time=datetime.datetime(year=2020, month=5, day=3, hour=23, minute=59, second=59),
    # )
    # gather_comment_info_for_all_video(connection=con)
    # gather_dm_info_for_all_video(connection=con)
